IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[SelectCocinaByFechaAndEstado]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[SelectCocinaByFechaAndEstado]
GO

CREATE PROCEDURE [dbo].[SelectCocinaByFechaAndEstado]
	(
		@fecha datetime = NULL,
		@estado char(1)
	)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @desde DATETIME
	DECLARE @hasta DATETIME
	
	IF @fecha IS NOT NULL
	BEGIN
		SET @desde = DATEADD(day, -1, @fecha)
		SET @hasta = DATEADD(day, 1, @fecha)
	END
	
	SELECT
	C.[cocinaID],
	C.[fecha],
	C.[personalID],
	C.[loteID],
	C.[almacenID],
	C.[cantidad],
	C.[estado],
	I.nombre as insumo
	FROM [Cocina] AS C
	INNER JOIN Lotes AS L ON C.loteID = L.loteID
	INNER JOIN Insumos AS I ON L.insumoID = I.insumoID
	WHERE C.[estado] = @estado AND
	CASE 
		WHEN @fecha IS NULL THEN 1
		WHEN C.[fecha] BETWEEN @desde AND @hasta THEN 1
		ELSE 0
	END = 1
	
	SET NOCOUNT OFF
END

GO
